package com.tallysystem.daoimpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.test.util.JdbcUtil;
import com.tallysystem.dao.UserDao;
import com.tallysystem.entity.User;

public class UserDaoImpl implements UserDao {

	//登录功能实现
	@Override
	public User getNumberAndPassword(Integer number, String password) {
		User user = null;
		try {
			Connection con = JdbcUtil.getConnection();
			String sql = "select * from user where number =?  and password =?";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setInt(1, number);
			stm.setString(2, password);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			while (res.next()) {
				Integer num = res.getInt("number");
				String pwd = res.getString("password");
				user = new User(num,pwd);
				break;
			}
	
			JdbcUtil.free(res, stm, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	
	//注册功能实现
	@Override
	public void add(User user) {
		try {
			Connection con = JdbcUtil.getConnection();
			String sql = "insert into  user(username,password,number,id,stuclass,sex,birth) values(?,?,?,?,?,?,?) ";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setString(1, user.getUsername());
			stm.setString(2, user.getPassword());
			stm.setInt(3, user.getNumber());
			stm.setInt(4, user.getId());
			stm.setString(5, user.getStuclass());
			stm.setString(6, user.getSex());
			stm.setDate(7, user.getBirth());
			
			// 4. 执行SQL语句
			int res = stm.executeUpdate();
			
			JdbcUtil.free(null, stm, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//显示所有帐号功能
	@Override
	public List<User> findUserMessage() {
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select * from `user` ";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			List<User> usermessage = new ArrayList<>();
			while (res.next()) {
				int id = res.getInt("id");
				int number = res.getInt("number");
				String username = res.getString("username");
				String password = res.getString("password");				
				String stuclass = res.getString("stuclass");
				String sex = res.getString("sex");
				Date birth = res.getDate("birth");
				usermessage.add(new User(username, password,number,id, stuclass,sex,birth));
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);
			
			return usermessage;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}

		return null;

	}
	
	//查询功能
	@Override
	public User findByname(String name) {
		User usertext = null;
		try {
			Connection con = JdbcUtil.getConnection();
			String sql = "select * from user where username =? ";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setString(1, name);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
//			List<User> userOne = new ArrayList<>();
			while (res.next()) {
				usertext = new User();
				usertext.setUsername(res.getString("username"));
				usertext.setPassword(res.getString("password"));
				usertext.setId(res.getInt("id"));
				usertext.setNumber(res.getInt("number"));
				usertext.setStuclass(res.getString("stuclass"));
				usertext.setSex(res.getString("sex"));
				usertext.setBirth(res.getDate("birth"));
			}
			JdbcUtil.free(res, stm, con);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return usertext;
	}
	
	//修改功能
	@Override
	public int updateByname(User user) {
		int res = 0;
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "update user set username=?,password=?,number=?,id=?,stuclass=?,sex=?,birth=? where username=?";
					// 3. 创建Statement
					// 4. 执行SQL语句
					PreparedStatement stm = con.prepareStatement(sql);
					//5.处理结果
					stm.setString(1, user.getUsername());
					stm.setString(2, user.getPassword());
					stm.setInt(3, user.getNumber());
					stm.setInt(4, user.getId());
					stm.setString(5, user.getStuclass());
					stm.setString(6, user.getSex());
					stm.setDate(7, user.getBirth());
					stm.setString(8, user.getUsername());
					// 4. 执行SQL语句
					res = stm.executeUpdate();	
					JdbcUtil.free(null, stm, con);
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
				return 0;
				
			

	}
	
	
	//删除帐号
	@Override
	public int deleteUser(String name) {
		int res = 0;
		// 1. 加载驱动
				try {
					// 2. 建立连接
					Connection con = JdbcUtil.getConnection();
					String sql = "delete from user where username=?";
					// 3. 创建Statement
					// 4. 执行SQL语句
					PreparedStatement stm = con.prepareStatement(sql);
					//5.处理结果
					stm.setString(1, name);
					// 4. 执行SQL语句
					res = stm.executeUpdate();	
					JdbcUtil.free(null, stm, con);
				} 
				catch (SQLException e) {
					e.printStackTrace();
				}
				return 0;
				
			
	}





	
		
}
